


ALTER     function fn_return_previous_lot_info_reports(@as_of_date varchar(20), @lot_id  int, @process varchar (20))
returns @fn_results Table( Lot_ID int,  Last_Created_On datetime ,Last_Created_Qty int, Penultimate_Created_On datetime, 
			Penultimate_Created_Qty int)

AS
begin
	declare @results Table(Lot_ID int,  Last_Created_On datetime ,Last_Created_Qty int, Penultimate_Created_On datetime, 
			Penultimate_Created_Qty int)
	declare @temp_lot_id1 int,@temp_lot_id2 int
	declare @temp_created_on1 datetime, @temp_created_on2 datetime
	declare @temp_qty1 int, @temp_qty2 int
	if @process= 'fab'
	begin	
		--set @table_name = @query_temp_table
		select @temp_lot_id1 = Lot_ID,  @temp_created_on1 =FL_Created_On,@temp_qty1 =FL_Input_Quantity from fab_lot_history where lot_id = @lot_id and convert(varchar,FL_Created_On,101) =convert(varchar,@as_of_date,101) 
		if @temp_lot_id1 is not null
		begin
			declare createdCursor cursor for
			select  FL_Created_On,FL_Input_Quantity from fab_lot_history where lot_id = @lot_id and FL_Created_On <@as_of_date order by FL_Created_On desc
			open createdCursor
			fetch next from createdCursor into @temp_created_on2,@temp_qty2
		
			while  @@fetch_status = 0
			begin	
				insert @results values( @temp_lot_id1, @temp_created_on1,@temp_qty1,@temp_created_on2,@temp_qty2)
				break
			end	
			close createdCursor
			deallocate createdCursor
		end
	end
	
	if @process= 'sort'
	begin	
		--set @table_name = @query_temp_table
		--select Lot_ID,  Timestamp,SL_wafer_Qty from sort_lot_history where lot_id = @lot_id order by Timestamp desc
		select @temp_lot_id1 = Lot_ID,  @temp_created_on1 =FL_Created_On,@temp_qty1 =FL_Input_Quantity from fab_lot_history where lot_id = @lot_id and convert(varchar,timestamp,101) =convert(varchar,@as_of_date,101) 
		if @temp_lot_id1 is not null
		begin
			declare createdCursor cursor for
			select Timestamp,SL_wafer_Qty from sort_lot_history where lot_id = @lot_id and Timestamp <@as_of_date order by timestamp desc
			open createdCursor
			fetch next from createdCursor into @temp_created_on2,@temp_qty2
		
			while  @@fetch_status = 0
			begin	
				insert @results values( @temp_lot_id1, @temp_created_on1,@temp_qty1,@temp_created_on2,@temp_qty2)
				break
			end	
			close createdCursor
			deallocate createdCursor
		end

	end
	
	if @process= 'assembly'
	begin	
		--set @table_name = @query_temp_table
		--select Lot_ID,  Timestamp, AL_Qty_In from assembly_lot_history where lot_id = @lot_id order by Timestamp desc
		select @temp_lot_id1 = Lot_ID,  @temp_created_on1 =Timestamp,@temp_qty1 =AL_Qty_In from assembly_lot_history where lot_id = @lot_id and convert(varchar,timestamp,101) =convert(varchar,@as_of_date,101) 
		if @temp_lot_id1 is not null
		begin
			declare createdCursor cursor for
			select Timestamp,AL_Qty_in from assembly_lot_history where lot_id = @lot_id and Timestamp <@as_of_date order by timestamp desc
			open createdCursor
			fetch next from createdCursor into @temp_created_on2,@temp_qty2
		
			while  @@fetch_status = 0
			begin	
				insert @results values( @temp_lot_id1, @temp_created_on1,@temp_qty1,@temp_created_on2,@temp_qty2)
				break
			end	
			close createdCursor
			deallocate createdCursor
		end
	end

	if @process= 'test'
	begin	
		--set @table_name = @query_temp_table
		--select Lot_ID,  Timestamp, FTL_Qty_In from final_test_lot_history where lot_id = @lot_id order by Timestamp desc
		select @temp_lot_id1 = Lot_ID,  @temp_created_on1 =Timestamp,@temp_qty1 =FTL_Qty_In from final_test_lot_history where lot_id = @lot_id and convert(varchar,timestamp,101) =convert(varchar,@as_of_date,101) 
		if @temp_lot_id1 is not null
		begin
			declare createdCursor cursor for
			select Timestamp,FTL_Qty_In from final_test_lot_history where lot_id = @lot_id and Timestamp <@as_of_date order by timestamp desc
			open createdCursor
			fetch next from createdCursor into @temp_created_on2,@temp_qty2
		
			while  @@fetch_status = 0
			begin	
				insert @results values( @temp_lot_id1, @temp_created_on1,@temp_qty1,@temp_created_on2,@temp_qty2)
				break
			end	
			close createdCursor
			deallocate createdCursor
		end
	end

	if @process= 'inventory'
	begin	
		--set @table_name = @query_temp_table
		--select Lot_ID,  Timestamp, IL_Qty_In from inventory_lot_history where lot_id = @lot_id order by Timestamp desc
		select @temp_lot_id1 = Lot_ID,  @temp_created_on1 = Timestamp,@temp_qty1 =IL_Qty_In from inventory_lot_history where lot_id = @lot_id and convert(varchar,timestamp,101) =convert(varchar,@as_of_date,101) 
		if @temp_lot_id1 is not null
		begin
			declare createdCursor cursor for
			select Timestamp,IL_Qty_In from inventory_lot_history where lot_id = @lot_id and Timestamp <@as_of_date order by timestamp desc
			open createdCursor
			fetch next from createdCursor into @temp_created_on2,@temp_qty2
		
			while  @@fetch_status = 0
			begin	
				insert @results values( @temp_lot_id1, @temp_created_on1,@temp_qty1,@temp_created_on2,@temp_qty2)
				break
			end	
			close createdCursor
			deallocate createdCursor
		end
	
	
	end

	insert @fn_results select *from @results
	return
end





